package com.china08.yxyapi.repository.db;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.china08.yxyapi.entity.db.lessons.YxyLessonsSynchroCatalog;

public interface YxyLessonsSynchroCatalogRepository extends JpaRepository<YxyLessonsSynchroCatalog, String> {

    @Query(value = "SELECT * FROM yxy_lessons.yxy_lessons_synchro_catalog GROUP BY stage_id", nativeQuery = true)
    List<YxyLessonsSynchroCatalog> findBySatagIdAll();

    @Query(value = "SELECT * FROM yxy_lessons.yxy_lessons_synchro_catalog WHERE stage_id = ?1 GROUP BY subject_id", nativeQuery = true)
    List<YxyLessonsSynchroCatalog> findBySubjectIdAll(String stageId);

    @Query(value = "SELECT * FROM yxy_lessons.yxy_lessons_synchro_catalog WHERE stage_id =?1 GROUP BY version_id", nativeQuery = true)
    List<YxyLessonsSynchroCatalog> findByVersionIdAll1(String stageId);

    @Query(value = "select * from yxy_lessons.yxy_lessons_synchro_catalog t1 left join yxy_lessons.yxy_lessons_synchro_grade t2 on t1.grade_id = t2.id where t1.stage_id = ?1 group by t1.grade_id order by t2.order_num asc", nativeQuery = true)
    List<YxyLessonsSynchroCatalog> findByGradeIdAll1(String stageId);

    @Query(value = "SELECT * FROM yxy_lessons.yxy_lessons_synchro_catalog WHERE stage_id=?1 AND subject_id =?2 GROUP BY version_id", nativeQuery = true)
    List<YxyLessonsSynchroCatalog> findByVersionIdAll2(String stageId, String subjectId);

    @Query(value = "SELECT * FROM yxy_lessons.yxy_lessons_synchro_catalog WHERE stage_id=?1 AND subject_id =?2 GROUP BY grade_id", nativeQuery = true)
    List<YxyLessonsSynchroCatalog> findByGradeIdAll2(String stageId, String subjectId);

    @Query(value = "select  *from  yxy_lessons.yxy_lessons_synchro_catalog t1 left join yxy_lessons.yxy_lessons_synchro_grade t2 on t1.grade_id = t2.id where t1.stage_id = ?1   and t1.subject_id = ?2   and t1.version_id = ?3 group by t1.grade_id order by t2.order_num", nativeQuery = true)
    List<YxyLessonsSynchroCatalog> findByGradeIdAll3(String stageId, String subjectId, String versionId);

    YxyLessonsSynchroCatalog findById(String catalogId);

    List<YxyLessonsSynchroCatalog> findByGradeId(String gradeId);

    @Query(value = "SELECT * FROM yxy_lessons.yxy_lessons_synchro_catalog WHERE stage_id=?1 AND version_id =?2 GROUP BY grade_id", nativeQuery = true)
    List<YxyLessonsSynchroCatalog> findByStageIdAndVersionAll(String stageId, String versionId);

    @Query(value = "SELECT * FROM yxy_lessons.yxy_lessons_synchro_catalog WHERE stage_id=?1 AND subject_id =?2 GROUP BY grade_id", nativeQuery = true)
    List<YxyLessonsSynchroCatalog> findByStageIdAndSubjectAll(String stageId, String subjectId);

}
